Karura

Row

Components of Total Value Locked

Row

Overall Total Value Locked (TVL)

  • The Overall TVL in the table below does not includes the Crowdloan TVL, which also seems to be the case on the http://stats.karura.network site.
Date Block dexTVL bridgeTVL liquidStakingTVL crowdloanTVL LPTokenStakingTVL stableCoinTVL OverallTVL
2022-06-21 2,135,922 3,727,008 13,675,207 9,799,394 26,940,660 3,653,256 11,270,679 42,125,544
2022-06-22 2,142,278 3,634,993 13,363,716 9,604,004 26,269,636 3,555,480 10,997,083 41,155,276
2022-06-23 2,148,575 3,646,287 14,090,545 9,918,909 27,162,162 3,593,146 11,193,642 42,442,529
2022-06-24 2,155,139 3,752,477 14,986,389 10,567,805 28,917,147 3,721,508 11,840,803 44,868,982
2022-06-25 2,161,562 3,759,708 14,905,610 10,473,270 28,632,500 3,750,830 11,689,145 44,578,563
2022-06-26 2,168,013 3,851,061 13,132,847 9,304,393 26,522,711 3,769,934 11,280,487 41,338,722
2022-06-27 2,173,904 3,734,609 13,187,844 9,347,125 26,615,421 3,767,156 11,178,714 41,215,449
2022-06-28 2,178,195 3,752,755 12,722,099 9,052,519 25,664,763 3,730,180 10,944,605 40,202,157

Row

Sources and Notes

Please click on the Source Code link at the top of the document to see the full source code. Here is a summary:

dexTVL

  • sum of tvlUSD from the xx function in the subscanr package for R. You can also use the Karura-dex project hosted on Subquery Network with this query:
{ 
  dailyPools { 
    nodes { 
      timestamp token0 {id} token1 {id} feeRateUSD dailyTradeVolumeUSD totalTVL txCount updateAtBlock {id}  
    } 
  } 
}

LPTokenStakingTVL

  • ACA (or KAR) obtained from the py-substrate-interface using function substrate.query(module=‘Rewards’,storage_function=‘PoolInfos’, block_hash = hash)
  • mulitplied by ACA (or KAR) price for the specific block number

BridgeTVL

  • KSM (or DOT) obtained from the py-substrate-interface using function substrate.query(module=‘Tokens’,storage_function=‘TotalIssuance’, params = [{‘Token’: ‘KSM’}], block_hash = hash)
  • LKSM (or LDOT) obtained from function substrate.query(module=‘Tokens’,storage_function=‘TotalIssuance’, params = [{‘Token’: ‘LKSM’}], block_hash = hash)
  • both mulitplied by KSM (or DOT) price for the specific block number

liquidStakingTVL

  • KSM (or DOT) obtained from the py-substrate-interface using function substrate.query(module=‘Homa’,storage_function=‘TotalStakingBonded’, block_hash = hash)
  • mulitplied by KSM (or DOT) price for the specific block number

lcdotTVL (Acala only)

  • LCDOT obtained from the py-substrate-interface using function substrate.query(module=‘Tokens’,storage_function=‘TotalIssuance’, params = [{‘LiquidCrowdloan’: 13}], block_hash = hash)
  • mulitplied by DOT price for the specific block number

stableCoinTVL

{ 
  dailyCollaterals { 
    nodes { 
      collateral {id} depositAmount debitAmount depositVolumeUSD debitVolumeUSD
      depositChangedUSD debitChangedUSD debitExchangeRate timestamp txCount 
    } 
  } 
}
---
title: "Acala / Karura TVL Dashboard"
output:
  flexdashboard::flex_dashboard:
    orientation: rows
    vertical_layout: scroll
    social: menu
    source_code: embed
params:
  network: Karura
  window: 7
  
---

```{css custom1, echo=FALSE}
.dataTables_scrollBody {
    max-height: 100% !important;
}
```

```{r global, include=FALSE}
library(knitr)
knitr::opts_chunk$set(
  message = FALSE,
  warning = FALSE,
  comment = "#>"
)

library(ggplot2)
library(kableExtra)
library(formattable)
library(lubridate)
library(flexdashboard)
library(DT)
library(subscanr)
library(formattable)
library(ghql)
x <- GraphqlClient$new()

# Helper function to concat
`%+%` <- function(a, b) paste0(a, b)

window = params$window
# window <- today() - as.Date("2021-12-31") + 1
network = params$network

# Liquidity Pool TVL ($3.72M) = totalDefiTvl = totalDexTvl + totalDexLockedTvl + homaLocked + stableCoinTvl
dex <- getDailyPools_acala_dex(network, window) %>%
  setorder(Date, pair)

dailyTVL <- dex[, .(max(updateAtBlock.id), sum(tvlUSD)), by = Date] %>%
  setnames(c("V1","V2"), c("Block","dexTVL"))

library(reticulate)
# use_python("/opt/homebrew/bin/python3.9")
```

```{python, include=FALSE}
from substrateinterface import SubstrateInterface

import pandas as pd

def getTotalIssuanceKSM():
    url = 'wss://karura.polkawallet.io'
    substrate = SubstrateInterface(url)
    hash = substrate.get_block_hash(block_id)
    timestamp = substrate.query(module='Timestamp',storage_function='Now',block_hash=hash).value
  
    # Issuance
    ksm = substrate.query(module='Tokens',storage_function='TotalIssuance', params = [{'Token': 'KSM'}], block_hash = hash)
    lksm = substrate.query(module='Tokens',storage_function='TotalIssuance', params = [{'Token': 'LKSM'}], block_hash = hash)
    result = substrate.query('AcalaOracle', 'Values', params = [{'Token': 'KSM'}], block_hash = hash)
  
    # homa = substrate.query(module='Homa',storage_function='TotalStakingBonded', block_hash = hash)
    try:
        homa = substrate.query_map(module='Homa',storage_function='StakingLedgers', block_hash = hash)
        amount = 0
        for res in homa:
            amount += res[1].value['bonded']
            
        # LKSM to KSM ratio
        ratio = lksm.value / amount 

    except:
        amount = 0
        # LKSM to KSM ratio
        ratio = 8.5
    
    # Build price DataFrame
    p = substrate.query('AcalaOracle', 'Values', params = [{'Token': 'KSM'}], block_hash = hash)
    price = p.value['value'] / 10**18
  
    bridge = (float(ksm.value / 10**12) + float(lksm.value / 10**12 / ratio)) * price
    liquidKSM = float(amount / 10**12) * price
    crowdloanTVL = (501137661910050505 / 10**12) * price
    return [bridge, liquidKSM, crowdloanTVL, block_id, ratio]

def getOraclePrices():
    if r.network=="Karura":
        url = 'wss://karura.polkawallet.io'
    else:
        url = 'wss://acala-rpc-0.aca-api.network'
      
    substrate = SubstrateInterface(url)
    hash = substrate.get_block_hash(block_id)
    timestamp = substrate.query(module='Timestamp',storage_function='Now',block_hash=hash).value
    p = substrate.query_map('AcalaOracle', 'Values', block_hash = hash)
    data = []
    for res in p:
        outi = {"token": str(res[0].value), "price": res[1].value['value'] / 10**18}
        data.append(outi)
    
    out = pd.DataFrame(data)
    return out
 
 
def getRewards():
    if r.network=="Karura":
        url = 'wss://karura.polkawallet.io'
    else:
        url = 'wss://acala-rpc-0.aca-api.network'
      
    substrate = SubstrateInterface(url)
    hash = substrate.get_block_hash(block_id)
    timestamp = substrate.query(module='Timestamp',storage_function='Now',block_hash=hash).value
  
    data = []
    rewards = substrate.query_map(module='Rewards',storage_function='PoolInfos', block_hash = hash)
    for res in rewards:
        if res[0][0] == 'Dex':
            pair = res[0].value['Dex']['DexShare']
            lp = substrate.query(module='Tokens',storage_function='TotalIssuance', params = [{'DexShare': pair}], block_hash = hash)
            legs = substrate.query(module='Dex',storage_function='LiquidityPool', params = [pair], block_hash = hash)
            outi = {"pair0": str(pair[0]), "pair1": str(pair[1]), "lp": str(lp.value), "leg0": str(legs[0].value), "leg1": str(legs[1].value)}
            data.append(outi)
    
    out = pd.DataFrame(data)
    return out

def getTotalIssuanceDOT():
    url = 'wss://acala-rpc-0.aca-api.network'
    substrate = SubstrateInterface(url)
    hash = substrate.get_block_hash(block_id)
    timestamp = substrate.query(module='Timestamp',storage_function='Now',block_hash=hash).value
  
    # Total Issuance data
    dot = substrate.query(module='Tokens',storage_function='TotalIssuance', params = [{'Token': 'DOT'}], block_hash = hash)
    ldot = substrate.query(module='Tokens',storage_function='TotalIssuance', params = [{'Token': 'LDOT'}], block_hash = hash)
    lcdot = substrate.query(module='Tokens',storage_function='TotalIssuance', params = [{'LiquidCrowdloan': 13}], block_hash = hash)
    
    # homa = substrate.query(module='Homa',storage_function='TotalStakingBonded', block_hash = hash)
    try:
        homa = substrate.query_map(module='Homa',storage_function='StakingLedgers', block_hash = hash)
        amount = 0
        for res in homa:
            amount += res[1].value['bonded']
            
        # LKSM to KSM ratio
        ratio = ldot.value / amount 

    except:
        amount = 0
        # LDOT to DOT ratio
        ratio = 10
    
    # Build price DataFrame
    tmp = []
    p = substrate.query_map('AcalaOracle', 'Values', block_hash = hash)
    outi = {"id": {'Token': 'AUSD'}, "price": 1}
    tmp.append(outi)
    for res in p:
      outi = {"id": res[0].value, "price": res[1].value['value'] / 10**18}
      tmp.append(outi)
      if res[0].value=={'Token': 'DOT'}:
        outi = {"id": {'LiquidCrowdloan': 13}, "price": res[1].value['value'] / 10**18 / 1.4}
        tmp.append(outi)
        outi = {"id": {'Token': 'LDOT'}, "price": res[1].value['value'] / 10**18 / ratio}
        tmp.append(outi)
  
    # Price lookup function
    p = substrate.query('AcalaOracle', 'Values', params = [{'Token': 'DOT'}], block_hash = hash)
    price = p.value['value'] / 10**18
    
    # Decimals lookup function
    tmp = [{'id': {'Token': 'ACA'}, 'decimals': 12}, {'id': {'Token': 'DOT'}, 'decimals': 10}, {'id': {'Token': 'LDOT'}, 'decimals': 10}, {'id': {'LiquidCrowdloan': 13}, 'decimals': 10}, {'id': {'Token': 'AUSD'}, 'decimals': 12}]
    decimals = pd.DataFrame(tmp)
    def getDecimals(token):
      return int(decimals[decimals['id'] == token]['decimals'])        
  
    bridge = (float(dot.value / 10**10) + float(ldot.value / 10**10 / ratio)) * price
    liquidDOT = float(amount / 10**10) * price
    lcDOT = float(lcdot.value / 10**10) * price
    crowdloanTVL = (325159802323576263 / 10**10) * price
    return [bridge, liquidDOT, crowdloanTVL, lcDOT, block_id, ratio]

```



```{r tvl, cache = TRUE, include=FALSE}

# dailyTVL[, M := month(Date) %+% year(Date)]
# dailyTVL[, maxDate := max(Date), by = M]
# dailyTVL <- dailyTVL[Date == maxDate]

# LP Token Staking TVL = totalDexTvl = useTotalDexPoolTVL()
# totalDexLockedTvl = useTotalStaking() = api.query.dex?.liquidityPool
# KSM Bridge TVL = totalIssuanceKSM = useTotalIssuance('KSM') = api.query.tokens?.totalIssuance
dailyTVL[, bridgeTVL := 0]
# Liquid KSM TVL = totalLocked in Homa = useState()
dailyTVL[, liquidStakingTVL := 0]
# homeLocked = totalStaking * price
dailyTVL[, crowdloanTVL := 0]
dailyTVL[, LPTokenStakingTVL := 0]
if (tolower(network) == "acala") dailyTVL[, lcdotTVL := 0]

for (i in 1:nrow(dailyTVL)) {
  py_run_string("block_id = " %+% dailyTVL$Block[i])
  if (tolower(network) == "karura") {
    tmp = py$getTotalIssuanceKSM()
  } else {
    tmp = py$getTotalIssuanceDOT()
    dailyTVL$lcdotTVL[i] <- tmp[4]
  }
  dailyTVL$bridgeTVL[i] <- tmp[1]
  dailyTVL$liquidStakingTVL[i] <- tmp[2]
  dailyTVL$crowdloanTVL[i] <- tmp[3]
  
  # LPTokenStakingTVL 
  rewards1 <- py$getRewards()
  rewards <- as.data.table(rewards1)
  # sort(unique(c(rewards$pair0, rewards$pair1)))
  rewards[, pair0 := fixToken(pair0)]
  rewards[, pair1 := fixToken(pair1)]
  rewards <- merge(rewards, tokens, by.x = 'pair0', by.y="Token")
  setnames(rewards, "decimals", "decimals0")
  rewards[, Name := NULL]
  rewards <- merge(rewards, tokens, by.x = 'pair1', by.y="Token")
  setnames(rewards, "decimals", "decimals1")
  rewards[, Name := NULL]
  rewards[, decimals0 := as.numeric(decimals0)]
  rewards[, decimals1 := as.numeric(decimals1)]
  rewards[, lpDecimals := 12]
  rewards[pair0=='DOT' & pair1=='LCDOT', lpDecimals := 10]
  rewards[, lp := as.numeric(lp) / 10**lpDecimals]
  rewards[, leg0 := as.numeric(leg0) / 10**decimals0]
  rewards[, leg1 := as.numeric(leg1) / 10**decimals1]
  
  price = py$getOraclePrices() %>%
    as.data.table
  price[, token := fixToken(token)]
  
  rewards <- merge(rewards, price, by.x = "pair0", by.y="token", all.x = TRUE)
  setnames(rewards, "price", "price0")
  rewards <- merge(rewards, price, by.x = "pair1", by.y="token", all.x = TRUE)
  setnames(rewards, "price", "price1")
  rewards[pair0 %in% c('KUSD', "AUSD"), price0 := 1]
  rewards[pair1 %in% c('KUSD', "AUSD"), price1 := 1]
  rewards[, tvl := leg0 * price0 + leg1 * price1]
  rewards[is.na(tvl), tvl := (leg0 * price0) * 2]
  rewards[is.na(tvl), tvl := (leg1 * price1) * 2]
  rewards[, lpPrice := tvl / lp]  
  dailyTVL$LPTokenStakingTVL[i] <- sum(rewards$tvl, na.rm = TRUE)

}



# stableCoinTvl
# const stableCoinTVL = useTotalLocked(); = api.query.loans?.totalPositions
collateral <- getLoansDailyCollateral_acala_loan(network, window, staging = FALSE) %>%
  setorder(Date, collateral.id)
stableCoinTvl <- collateral[, sum(depositVolumeUSD), by = Date] %>%
  setnames("V1", "stableCoinTVL")
dailyTVL <- merge(dailyTVL, stableCoinTvl, by = "Date", all.x = TRUE)

# If monthly, remove these fields
try(dailyTVL[, M := NULL])
try(dailyTVL[, maxDate := NULL])

# Make columns numeric
for (col in names(dailyTVL)[-1]) set(dailyTVL, j=col, value=as.numeric(dailyTVL[[col]])) 
if (tolower(network) == "acala") {
  dailyTVL[, OverallTVL := dexTVL + LPTokenStakingTVL + bridgeTVL + liquidStakingTVL + lcdotTVL + stableCoinTVL]
} else {
  dailyTVL[, OverallTVL := dexTVL + LPTokenStakingTVL + bridgeTVL + liquidStakingTVL + stableCoinTVL]
}

# merge history
history <- fread(network %+% "_TVL.csv")
history[, Date := as.Date(Date)]
mindate <- min(dailyTVL$Date)
dailyTVL <- rbind(history[Date < mindate], dailyTVL, use.names = TRUE)
fwrite(dailyTVL, file = network %+% "_TVL.csv")


```

# `r network` {.tabset}

Row
----

### Components of Total Value Locked

```{r k_plot1}

if (tolower(network) == "acala") {
  dat <- melt(data = dailyTVL[, .(Date, dexTVL, LPTokenStakingTVL, bridgeTVL, liquidStakingTVL, lcdotTVL, stableCoinTVL)], 
            id.vars = "Date",
            value.name = "TVL")
  cols <- c('dexTVL', 'LPTokenStakingTVL', 'bridgeTVL', 'liquidStakingTVL', 'LCDOTTVL', 'stableCoinTVL')
} else {
  dat <- melt(data = dailyTVL[, .(Date, dexTVL, LPTokenStakingTVL, bridgeTVL, liquidStakingTVL, stableCoinTVL)], 
            id.vars = "Date",
            value.name = "TVL")
  cols <- c('dexTVL', 'LPTokenStakingTVL', 'bridgeTVL', 'liquidStakingTVL', 'stableCoinTVL')
  
}
dat[, TVL := TVL / 1e6]
 
# Stacked
ggplot(dat, aes(fill=variable, y=TVL, x=Date)) + 
    geom_bar(position="stack", stat="identity") +
    ggtitle(label="Components of Total Value Locked") + 
    ylab("Totval Value Locked (in millions USD)")

```

Row
----

### Overall Total Value Locked (TVL)

* *The Overall TVL in the table below does not includes the Crowdloan TVL, which also seems to be the case on the http://stats.`r tolower(network)`.network site.*

```{r k_tvl}

knitr::kable(dailyTVL, escape = FALSE, format.args = list(big.mark = ",")) %>%
  kable_styling()

```

Row
----

### Sources and Notes

Please click on the *Source Code* link at the top of the document to see the full source code.  Here is a summary:

#### dexTVL 

- sum of tvlUSD from the xx function in the [subscanr](https://github.com/rogerjbos/subscanr) package for R.  You can also use the [Karura-dex](https://explorer.subquery.network/subquery/AcalaNetwork/karura-dex) project hosted on Subquery Network with this query:
```
{ 
  dailyPools { 
    nodes { 
      timestamp token0 {id} token1 {id} feeRateUSD dailyTradeVolumeUSD totalTVL txCount updateAtBlock {id}  
    } 
  } 
}
```

#### LPTokenStakingTVL
- ACA (or KAR) obtained from the [py-substrate-interface](https://github.com/polkascan/py-substrate-interface) using function *substrate.query(module='Rewards',storage_function='PoolInfos', block_hash = hash)*
- mulitplied by ACA (or KAR) price for the specific block number

#### BridgeTVL
- KSM (or DOT) obtained from the [py-substrate-interface](https://github.com/polkascan/py-substrate-interface) using function *substrate.query(module='Tokens',storage_function='TotalIssuance', params = [{'Token': 'KSM'}], block_hash = hash)*
- LKSM (or LDOT) obtained from function *substrate.query(module='Tokens',storage_function='TotalIssuance', params = [{'Token': 'LKSM'}], block_hash = hash)*
- both mulitplied by KSM (or DOT) price for the specific block number

#### liquidStakingTVL
- KSM (or DOT) obtained from the [py-substrate-interface](https://github.com/polkascan/py-substrate-interface) using function *substrate.query(module='Homa',storage_function='TotalStakingBonded', block_hash = hash)*
- mulitplied by KSM (or DOT) price for the specific block number

#### lcdotTVL (Acala only)
- LCDOT obtained from the [py-substrate-interface](https://github.com/polkascan/py-substrate-interface) using function *substrate.query(module='Tokens',storage_function='TotalIssuance', params = [{'LiquidCrowdloan': 13}], block_hash = hash)*
- mulitplied by DOT price for the specific block number

#### stableCoinTVL
- sum of depositVolumeUSD from the *getLoansDailyCollateral_acala_loan* function in the [subscanr](https://github.com/rogerjbos/subscanr) package for R.  You can also use the [Karura-loan]https://explorer.subquery.network/subquery/AcalaNetwork/karura-loan) project hosted on Subquery Network with this query:
```
{ 
  dailyCollaterals { 
    nodes { 
      collateral {id} depositAmount debitAmount depositVolumeUSD debitVolumeUSD
      depositChangedUSD debitChangedUSD debitExchangeRate timestamp txCount 
    } 
  } 
}
```